package com.rocky.ssm.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.rocky.ssm.model.Students;




/**
 * @Author Meice
 * @Date 2017年8月10日
 */
public class stuBaseDao extends BaseDao{
	//定义list方法，从仓库获取数据
	public List<Students> list() {
		List<Students> list = new ArrayList<>();
		//连接数据库，遍历存放和类建立关系
		conn = this.getConn();
		String sql = "select * from stu_info";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int stuId =	rs.getInt("stu_id");
				String stuName = rs.getString("stu_name");
				String stuGender = rs.getString("stu_gender");
				int stuAge	= rs.getInt("stu_age");
				String stuTel = rs.getString("stu_tel");
				String stuEmail = rs.getString("stu_email");
				String stuSchool = rs.getString("stu_school");
				Students sd = new Students(stuId, stuName, stuAge, stuGender, stuEmail, stuTel, stuSchool);	
				//把每条数据作为一个对象添加到list集合中
				list.add(sd);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			//关闭资源
			this.closeConnection(rs, ps, conn);
		}
		return list;
	}
	
	
	//新增学员的方法
	public int stuAdd(Students students) {
		int result = 0;
		String sql = "insert into stu_info (stu_name,stu_gender,stu_age,stu_tel,"
				+ "stu_email,stu_IDcard,stu_address,stu_birth,stu_school,stu_QQ,stu_parent_name,"
				+ "stu_province,stu_parent_tel,stu_city,stu_status,stu_content) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {students.getStuName(),students.getStuGender(),students.getStuAge(),students.getStuTel(),
			students.getStuEmail(),students.getStuIdcard(),students.getStuAddress(),students.getStuBirth(),students.getStuSchool(),
			students.getStuQq(),students.getStuParentName(),students.getStuProvince(),students.getStuParentTel(),students.getStuCity(),
			students.getStuStatus(),students.getStuContent()};	
		this.executeUpdate(sql,params);	
		return result;
	}
	
	
	//定义修改学员根据传递的id显示数据方法getStuById()
	public Students getStuById(int stu_id) {
		Students students = null;
		conn = this.getConn();
		String sql = "select * from stu_info where stu_id = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, stu_id);
			rs = ps.executeQuery();
//			后续思考如何优化？过多属性容易写错，而且多么枯燥啊
			if(rs.next()) {
				int stuId = rs.getInt("stu_id");
				String stuName = rs.getString("stu_name");
				String stuGender = rs.getString("stu_gender");
				int stuAge	= rs.getInt("stu_age");
				String stuTel = rs.getString("stu_tel");
				String stuEmail = rs.getString("stu_email");
				String stuIdcard = rs.getString("stu_IDcard");
				String stuAddress = rs.getString("stu_address");
				String stuBirth = rs.getString("stu_birth");
				String stuSchool = rs.getString("stu_school");
				String stuQq = rs.getString("stu_QQ");
				String stuParentName = rs.getString("stu_parent_name");
				String stuProvince = rs.getString("stu_province");
				String stuParentTel = rs.getString("stu_parent_tel");
				String stuCity = rs.getString("stu_city");
				String stuStatus = rs.getString("stu_status");
				String stuContent = rs.getString("stu_content");
				
				students = new Students(stuId,stuName,stuAge,
						 stuGender,stuIdcard,stuBirth,
						 stuEmail,stuTel, stuAddress,stuQq,
						 stuSchool, stuParentName,stuParentTel,
						 stuProvince,stuCity,stuStatus,
						 stuContent);
				
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return students;
	
	}
	
	//定义修改学员方法stuPayModify()
	public int stuPayModify(Students students) {
		int result =0;
		String sql = "update stu_info set stu_name = ?, stu_gender = ?,"
				+ " stu_age=?,stu_tel=?,stu_email=?,stu_IDcard=?,stu_address=?,"
				+ " stu_birth=?, stu_school=?,stu_QQ=?, stu_parent_name=?,"
				+ " stu_province=?,stu_parent_tel=?,stu_city=?,stu_status=?,stu_content=? where stu_id = ?";
		Object[] params = {students.getStuName(),students.getStuGender(),
				students.getStuAge(),students.getStuTel(),students.getStuEmail()
				,students.getStuIdcard(),students.getStuAddress(),students.getStuBirth(),
				students.getStuSchool(),students.getStuQq(),students.getStuParentName(),
				students.getStuProvince(),students.getStuParentTel(),students.getStuCity(),students.getStuStatus(),
				students.getStuContent(),students.getStuId()};
		this.executeUpdate(sql, params);	
		return result;
	}
	
	
	//定义删除学员方法stuDel()
	public int stuDel(int stu_id) {
		int result = 0;
		String sql = "delete from stu_info where stu_id = ?";
		Object[] params = {stu_id};
		this.executeUpdate(sql, params);
		return result;
	}
	
	//定义根据学员姓名查询的方法(和getStuById一模一样)
	
	public Students getStuByName(String stu_name) {
		Students students = null;
		conn = this.getConn();
		String sql = "select * from stu_info where stu_name = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stu_name);
			rs = ps.executeQuery();
//			后续思考如何优化？过多属性容易写错，而且多么枯燥啊
			if(rs.next()) {
				int stuId = rs.getInt("stu_id");
				String stuName = rs.getString("stu_name");

				String stuGender = rs.getString("stu_gender");
				int stuAge	= rs.getInt("stu_age");
				String stuTel = rs.getString("stu_tel");
				String stuEmail = rs.getString("stu_email");
				String stuIdcard = rs.getString("stu_IDcard");
				String stuAddress = rs.getString("stu_address");
				String stuBirth = rs.getString("stu_birth");
				String stuSchool = rs.getString("stu_school");
				String stuQq = rs.getString("stu_QQ");
				String stuParentName = rs.getString("stu_parent_name");
				String stuProvince = rs.getString("stu_province");
				String stuParentTel = rs.getString("stu_parent_tel");
				String stuCity = rs.getString("stu_city");
				String stuStatus = rs.getString("stu_status");
				String stuContent = rs.getString("stu_content");
				
				
				System.out.println(stuName);
				students = new Students(stuId,stuName,stuAge,
						 stuGender,stuIdcard,stuBirth,
						 stuEmail,stuTel, stuAddress,stuQq,
						 stuSchool, stuParentName,stuParentTel,
						 stuProvince,stuCity,stuStatus,
						 stuContent);	
			}	
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return students;
	}
	
	
	
}
